package dto;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Card;


public class DtoCard {
	
	public static List<Card> getAll(DataManager dataManager, String sql) {
		List<Card> cards = new ArrayList<Card>();
		
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		if (conn != null) {
			try {
				statement = conn.createStatement();
				
				rs = statement.executeQuery(sql);
				
				while (rs.next()) {
					Card card = new Card();
					card.setCardId(rs.getInt("cardId"));
					card.setMoney(rs.getInt("money"));
					card.setStatus(rs.getInt("status"));
					
					cards.add(card);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null) 
						rs.close();
					if(statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return cards;
	}
	
	public static List<Card> getAll(DataManager dataManager)
	{
		String sql = "select * from Card";
		return getAll(dataManager, sql);
	}
	
	public static int getMoneyByCardId(DataManager dataManager, int cardId)
	{
		String sql = "select * from Card where cardId  =" + cardId;
		List<Card> cards = getAll(dataManager, sql);
		//Card c = cards.get(0);
		if(cards.isEmpty())
		{
			return -1;
		}
		else
		{
			Card c = cards.get(0);
			if(c.getStatus()==0)
				return -1;
			else return c.getMoney();
		}
		
	}
	
	public static boolean updateStatusCard(DataManager dataManager, int cardId) {
		Connection conn = null;
		Statement statement = null;
		/*
		 * statust card = 0 : card nay da duoc su dung
		 * status card = 1 : card chua su dung
		 * */
		String sqlQuery = "update Card set status = 0  where cardId = " + cardId;
		//System.out.print(sqlQuery);
		boolean isOk = true;
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(sqlQuery);
		}
		catch (Exception e) {
			isOk = false;
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		return isOk;
	}
	
	public static int countCardByStatusAndMoney(DataManager dataManager, int money, int status)
	{
		String sql = "select * from card where status = "+status+" and money = "+money;
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		int count = 0;
		if (conn != null) {
			try {
				statement = conn.createStatement();
				rs = statement.executeQuery(sql);
				while (rs.next()) {
					count++;
				}
			} catch (SQLException e) {
				System.out.print("Loi truy van");
				e.printStackTrace();
			} finally {
				try {
					if (rs != null) 
						rs.close();
					if(statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return count;
		
	}
	public static boolean addCard(DataManager dataManager, int money)
	{
		String sqlQuery = "insert into card (money, status) values (" + money + ", 1)";
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		boolean isOk = true;
		if (conn != null) {
			try {
				statement = conn.createStatement();
				statement.executeUpdate(sqlQuery);
			} catch (SQLException e) {
				isOk = false;
				e.printStackTrace();
			} finally {
				try {
					if (rs != null) 
						rs.close();
					if(statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return isOk;
		
	}

}
